﻿using BCCommon;
using BCCommon.TMS.FreightEnums;
using BCEntity.TMS.FreightContact;
using Dapper;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;

namespace BCData.TMS.FreightContact
{
    public class FreightContactData : IFreightContactData
    {
        private const string QUERY = @"SELECT FreightContactId,UserId,ContactInitials,ContactName,ContactPhone,ContactType,CreateTime FROM FreightContact";
        private readonly IDatabaseContext databaseContext;

        public FreightContactData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }

        public async Task<FreightContactEntity> InsertAsync(FreightContactEntity entity)
        {
            string sql = SqlCommands.GetInsertSql("FreightContact", new string[] { "UserId", "ContactInitials", "ContactName", "ContactPhone", "ContactType" });
            sql = string.Format("{0} {1} WHERE FreightContactId=LAST_INSERT_ID();", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?UserId", entity.UserId, DbType.Int64);
            parameters.Add("?ContactInitials", entity.ContactInitials, DbType.AnsiString, size: 20);
            parameters.Add("?ContactName", entity.ContactName, DbType.AnsiString, size: 20);
            parameters.Add("?ContactPhone", entity.ContactPhone, DbType.AnsiString, size: 20);
            parameters.Add("?ContactType", entity.ContactType.ToString(), DbType.AnsiString);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<FreightContactEntity>(sql, parameters);
        }

        public async Task<bool> CheckFreightContactExistsAsync(long freightContactId)
        {
            string sql = @"SELECT 1 FROM FreightContact WHERE FreightContactId=?FreightContactId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?FreightContactId", freightContactId, DbType.Int64);
            var res = await this.databaseContext.Master.ExecuteScalarAsync<int>(sql, parameters);
            return res > 0;
        }

        public async Task<object> CheckFreightContactExistsAsync(long userId, string phone, FreightContactType freightContactType)
        {
            string sql = @"SELECT 1 FROM FreightContact WHERE UserId=?UserId AND ContactPhone=?ContactPhone And ContactType=?ContactType";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?UserId", userId, DbType.Int64);
            parameters.Add("?ContactPhone", phone, DbType.AnsiString, size: 20);
            parameters.Add("?ContactType", freightContactType.ToString(), DbType.AnsiString);
            return await this.databaseContext.Master.ExecuteScalarAsync(sql, parameters);
        }

        public async Task<object> CheckFreightContactExistsAsync(long freightContactId, long userId)
        {
            string sql = @"SELECT 1 FROM FreightContact WHERE UserId=?UserId AND FreightContactId=?FreightContactId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?UserId", userId, DbType.Int64);
            parameters.Add("?FreightContactId", freightContactId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteScalarAsync(sql, parameters);
        }

        public async Task<int> DeleteAsync(long freightContactId)
        {
            string sql = @"DELETE FROM FreightContact WHERE FreightContactId=?FreightContactId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?FreightContactId", freightContactId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters);
        }

        public async Task<IEnumerable<FreightContactEntity>> GetAsync(long userId, FreightContactType? freightContactType)
        {
            string sql = QUERY;
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("UserId=?UserId");
            parameters.Add("?UserId", userId, DbType.Int64);
            if (freightContactType.HasValue)
            {
                wheres.Add("ContactType=?ContactType");
                parameters.Add("?ContactType", freightContactType.Value.ToString(), DbType.AnsiString);
            }

            sql = string.Format("{0} {1}", sql, wheres.Count > 0 ? "WHERE " + string.Join(" AND ", wheres) : string.Empty);
            return await this.databaseContext.Slave.QueryAsync<FreightContactEntity>(sql, parameters);
        }

        public async Task<FreightContactEntity> GetAsync(long userId, string phone, FreightContactType freightContactType)
        {
            string sql = string.Format("{0} WHERE UserId=?UserId AND ContactPhone=?ContactPhone AND ContactType=?ContactType;", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?UserId", userId, DbType.Int64);
            parameters.Add("?ContactPhone", phone, DbType.AnsiString, size: 20);
            parameters.Add("?ContactType", freightContactType.ToString(), DbType.AnsiString);
            return await this.databaseContext.Master.QueryEntityAsync<FreightContactEntity>(sql, parameters);
        }

        public async Task<FreightContactEntity> UpdateAsync(FreightContactEntity entity)
        {
            string sql = SqlCommands.GetUpdateSql("FreightContact", new string[] { "ContactInitials", "ContactName", "ContactPhone", "ContactType" }, new string[] { "FreightContactId" });
            sql = string.Format("{0} {1} WHERE FreightContactId=?FreightContactId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?FreightContactId", entity.FreightContactId, DbType.Int64);
            parameters.Add("?ContactInitials", entity.ContactInitials, DbType.AnsiString, size: 20);
            parameters.Add("?ContactName", entity.ContactName, DbType.AnsiString, size: 20);
            parameters.Add("?ContactPhone", entity.ContactPhone, DbType.AnsiString, size: 20);
            parameters.Add("?ContactType", entity.ContactType.ToString(), DbType.AnsiString);
            return await this.databaseContext.Master.QueryEntityAsync<FreightContactEntity>(sql, parameters);
        }
    }
}